package de.realriot.ipsdroid.v1.beta;

import java.util.ArrayList;
import java.util.Hashtable;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import de.realriot.libs.ipsjson.IPSException;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class IPSDataHelper {

	// Define global database values.
	private static final String DATABASE_NAME = "ipsdroid.db";
	private static final int DATABASE_VERSION = 15;

	private Context parentContext;
	private String host;
	private SQLiteDatabase db;
	
	/**
     * Constructor of this database helper class.
     * 
     * @param context Previous context.
     * @param currentHost Hostname of the current IP-Symcon server.
     */
	public IPSDataHelper(Context context, String currentHost)
	{
		parentContext = context;
		host = currentHost;
	}

	/**
     * This function updates the host variable. Perhaps it has been changed
     * or initialized after creating this object.
     * 
     * @param ipshost Host of the ip-symcon server.
     */
	public void setHost(String ipshost)
	{
		host = ipshost;
	}	

	/**
     * Adds a host to the hostList database.
     * 
     * @param host Hostname or IP of the server.
     * @param port Port of the server.
     * @useSSL TRUE or FALSE to use SSL.
     * @username Username to login to the server.
     * @password Password to login to the server.
     * @throws SQLiteException
     * @throws IPSException
     */
	public void addHostToHostDB(String host, Integer port, Boolean useSSL,
			String username, String password) throws SQLiteException, IPSException
	{
		OpenHelper openHelper = new OpenHelper(parentContext);
		db = openHelper.getWritableDatabase();
			
		ContentValues values = new ContentValues();
		values.put("host", host);
		values.put("port", port);
		values.put("ssl", useSSL);
		values.put("username", username);
		values.put("password", password);
		
		long result = db.insert("ips_hosts", null, values);
		if ( result == -1 )
			throw new SQLiteException("Could not insert record.");

		db.close();
	}
	
	/**
     * Completely deletes a host from the database.
     * 
     * @param host Hostname of the IP-Symcon server.
     * @throws SQLiteException
     */
	public void deleteHostFromDB(String host) throws SQLiteException
	{
		OpenHelper openHelper = new OpenHelper(parentContext);
		db = openHelper.getWritableDatabase();
		
		// Delete host from hostlist.
		db.execSQL("DELETE from ips_hosts where host = '" + host + "'");
		
		// Delete cachedata.
		db.execSQL("DELETE from ips_objects where host = '" + host + "'");
		db.execSQL("DELETE from ips_events where host = '" + host + "'");
		db.execSQL("DELETE from ips_instances where host = '" + host + "'");
		db.execSQL("DELETE from ips_links where host = '" + host + "'");
		db.execSQL("DELETE from ips_media where host = '" + host + "'");
		db.execSQL("DELETE from ips_scripts where host = '" + host + "'");
		db.execSQL("DELETE from ips_variables where host = '" + host + "'");
		db.execSQL("DELETE from ips_varprofiles where host = '" + host + "'");

		db.close();
	}

	/**
     * Fetches a requested host from the database and returns
     * a structure which holds all needed connection informations. 
     * 
     * @param host Hostname or IP of the server.
     * @return A hashtable structure which all hostinformations.
     * @throws SQLiteException
     */
	public Hashtable<String,String> getHostFromDB(String host) throws SQLiteException
	{
		Hashtable<String,String> hostEntry = null;
		
		OpenHelper openHelper = new OpenHelper(parentContext);
		db = openHelper.getWritableDatabase();
		
		Cursor c = db.rawQuery("SELECT * from ips_hosts where host = '" + host + "'", null);
		
		// Check if we got a result.
		if ( c != null && c.getCount() == 1 )
		{
			c.moveToFirst();
			hostEntry = new Hashtable<String,String>();
			hostEntry.put("host", host);
			hostEntry.put("port", c.getString(1));
			
			if ( c.getString(2).equals("1") )
				hostEntry.put("ssl", "true");
			else
				hostEntry.put("ssl", "false");

			hostEntry.put("username", c.getString(3));
			hostEntry.put("password", c.getString(4));
		}
		
		c.close();
		db.close();
		
		return hostEntry;
	}

	/**
     * Querys the db for a configured hostlist. If there's a result,
     * the functions returns an arrayList of hosts.
     * 
     * @return ArrayList of hosts.
     * @throws SQLiteException
     */
	public ArrayList<String> getHostListFromDB() throws SQLiteException
	{
		ArrayList<String> result = new ArrayList<String>();
	
		OpenHelper openHelper = new OpenHelper(parentContext);
		db = openHelper.getWritableDatabase();

		Cursor c = db.rawQuery("SELECT host from ips_hosts", null);
		
		// Check if we got a result.
		if ( c != null && c.getCount() > 0 )
		{
			c.moveToFirst();
			do
			{
				result.add(c.getString(0));
			} while ( c.moveToNext() );
		}
		
		c.close();
		db.close();
		
		return result;
	}
	
	/**
     * Completely deletes the cachedata of a host from the database.
     * 
     * @param host Hostname of the IP-Symcon server.
     * @throws SQLiteException
     */
	public void deleteHostCacheFromDB(String host) throws SQLiteException
	{
		OpenHelper openHelper = new OpenHelper(parentContext);
		db = openHelper.getWritableDatabase();
		
		db.execSQL("DELETE from ips_objects where host = '" + host + "'");
		db.execSQL("DELETE from ips_events where host = '" + host + "'");
		db.execSQL("DELETE from ips_instances where host = '" + host + "'");
		db.execSQL("DELETE from ips_links where host = '" + host + "'");
		db.execSQL("DELETE from ips_media where host = '" + host + "'");
		db.execSQL("DELETE from ips_scripts where host = '" + host + "'");
		db.execSQL("DELETE from ips_variables where host = '" + host + "'");
		db.execSQL("DELETE from ips_varprofiles where host = '" + host + "'");
		
		db.close();
	}

	/**
     * Adds JSONObjects out of a JSONArray to the database. The IPS-Type
     * of the object is automatically handled.
     * 
     * @param objArr The IPS-JSON Object-Array.
     * @throws SQLiteException
     * @throws JSONException
	 * @throws IPSException 
     */
	public void addJSONArrayToDB(JSONArray objArr) throws SQLiteException, JSONException, IPSException
	{
		OpenHelper openHelper = new OpenHelper(parentContext);
		db = openHelper.getWritableDatabase();

		for ( int i = 0; i < objArr.length(); i++ )
		{
			JSONObject obj = objArr.getJSONObject(i);
			
			String table = "";
			String id = "";
			
			if ( obj != null )
			{
				// Find the correct db table and check the id.
				if ( obj.has("ObjectID") )
				{
					table = "ips_objects";
					id = obj.getString("ObjectID");
				}
				if ( obj.has("EventID") )
				{
					table = "ips_events";
					id = obj.getString("EventID");
				}
				if ( obj.has("InstanceID") )
				{
					table = "ips_instances";
					id = obj.getString("InstanceID");
				}
				if ( obj.has("LinkID") )
				{
					table = "ips_links";
					id = obj.getString("LinkID");
				}
				if ( obj.has("MediaID") )
				{
					table = "ips_media";
					id = obj.getString("MediaID");
				}
				if ( obj.has("ScriptID") )
				{
					table = "ips_scripts";
					id = obj.getString("ScriptID");
				}
				if ( obj.has("VariableID") )
				{
					table = "ips_variables";
					id = obj.getString("VariableID");
				}
				if ( obj.has("ProfileName") )
				{
					table = "ips_varprofiles";
					id = obj.getString("ProfileName");
				}
				
				// Check if the object already exists within the database.
				Cursor c = db.rawQuery(
						"SELECT id FROM " + table + " WHERE " + 
							"id = '" + id.toString() + "' and host = '" + host + "'", null);
				if ( c != null )
				{
					if ( c.getCount() == 0 )
					{
						ContentValues values = new ContentValues();
						values.put("id", id);
						values.put("host", host);
						values.put("json", obj.toString());
						
						long result = db.insert(table, null, values);
						if ( result == -1 )
							throw new SQLiteException("Could not insert record.");
					}
				}
			}
		}
		db.close();
	}
	
	/**
     * Adds a JSON object to the database. The IPS-Type of the object
     * is automatically handled.
     * 
     * @param obj The IPS-JSON Object.
	 * @throws SQLiteException
	 * @throws JSONException
	 * @throws IPSException 
     */
	public void addJSONObjectToDB(JSONObject obj) throws SQLiteException, JSONException, IPSException
	{
		String table = "";
		String id = "";
		
		if ( obj != null )
		{
			// Find the correct db table and check the id.
			if ( obj.has("ObjectID") )
			{
				table = "ips_objects";
				id = obj.getString("ObjectID");
			}
			if ( obj.has("EventID") )
			{
				table = "ips_events";
				id = obj.getString("EventID");
			}
			if ( obj.has("InstanceID") )
			{
				table = "ips_instances";
				id = obj.getString("InstanceID");
			}
			if ( obj.has("LinkID") )
			{
				table = "ips_links";
				id = obj.getString("LinkID");
			}
			if ( obj.has("MediaID") )
			{
				table = "ips_media";
				id = obj.getString("MediaID");
			}
			if ( obj.has("ScriptID") )
			{
				table = "ips_scripts";
				id = obj.getString("ScriptID");
			}
			if ( obj.has("VariableID") )
			{
				table = "ips_variables";
				id = obj.getString("VariableID");
			}
			if ( obj.has("ProfileName") )
			{
				table = "ips_varprofiles";
				id = obj.getString("ProfileName");
			}
	
			if (! dbRecordExists(table, "id", id.toString(), true) )
			{
				OpenHelper openHelper = new OpenHelper(parentContext);
				db = openHelper.getWritableDatabase();
					
				ContentValues values = new ContentValues();
				values.put("id", id);
				values.put("host", host);
				values.put("json", obj.toString());
				
				long result = db.insert(table, null, values);
				if ( result == -1 )
					throw new SQLiteException("Could not insert record.");
	
				db.close();
			}
		}
	}

	/**
     * Fetches a JSON-Object from the database, by a given ID and database table.
     * 
     * @param table The name of the database table, where the object is located.
     * @param id The id or name of the object.
     * @return The object which was hopefully found within the table; otherwise NULL.
     * @throws SQLiteException
     * @throws JSONException
     */
	public JSONObject getJSONObjectFromDB(String table, String id) throws SQLiteException, JSONException 
	{
		JSONObject result = null;
		
		OpenHelper openHelper = new OpenHelper(parentContext);
		db = openHelper.getWritableDatabase();

		Cursor c = db.rawQuery("SELECT * from " + table + " where id = '" + id + "' and host = '" +
				host + "'", null);
		
		// Check if we got a result.
		if ( c != null && c.getCount() == 1 )
		{
			c.moveToFirst();
			result = new JSONObject(c.getString(2));
		}
		
		c.close();
		db.close();
		
		return result;
	}

	/**
     * Checks wether a key exists within the datase.
     * 
     * @param dbtable The databasetable to search for the key.
     * @param idName The key to look for.
     * @param value Value of the key to search for.
     * @param checkHost Switch, if we have to check the value against a host.
     * @return TRUE if the key exists, otherwise FALSE.
     * @throws SQLiteException
     */
	public boolean dbRecordExists(String dbtable, String idName, String value, Boolean checkHost) throws SQLiteException
	{
		OpenHelper openHelper = new OpenHelper(parentContext);
		db = openHelper.getWritableDatabase();
		
		String withHostCheck = "";
		if ( checkHost )
			withHostCheck = " and host = '" + host + "'";
		
		Cursor c = db.rawQuery(
				"SELECT " + idName + " FROM " + dbtable + " WHERE " + 
					idName + " = '" + value + "'" + withHostCheck, null);

		if ( c != null )
		{
			if ( c.getCount() == 1 )
			{
				c.close();
				db.close();
				return true;
			} else
			{
				db.close();
			}
		} else
		{
			db.close();
		}
		return false;
	}

	/**
     * Clear all tables.
     * @throws SQLiteException
     */
	public void emptyTables() throws SQLiteException
	{
		OpenHelper openHelper = new OpenHelper(parentContext);
		db = openHelper.getWritableDatabase();

		db.delete("ips_hosts", null, null);
		db.delete("ips_objects", null, null);
		db.delete("ips_events", null, null);
		db.delete("ips_instances", null, null);
		db.delete("ips_links", null, null);
		db.delete("ips_media", null, null);
		db.delete("ips_scripts", null, null);
		db.delete("ips_variables", null, null);
		db.delete("ips_varprofiles", null, null);

		db.close();
	}
	
	/**
     * Helper to open the database connection and initially create tables.
     */
    private static class OpenHelper extends SQLiteOpenHelper
	{
		OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) throws SQLiteException {
			// Create host-connection-table.
			db.execSQL("CREATE TABLE ips_hosts " +
					"(host TEXT PRIMARY KEY," +
					"port INTEGER," +
					"ssl BOOLEAN," +
					"username TEXT," +
					"password TEXT)");

			// Create objects table.
			db.execSQL("CREATE TABLE ips_objects " +
					"(id TEXT," +
					"host TEXT," +
					"json TEXT)");
			// Create events table.
			db.execSQL("CREATE TABLE ips_events " +
					"(id TEXT," +
					"host TEXT," +
					"json TEXT)");
			// Create instances table.
			db.execSQL("CREATE TABLE ips_instances " +
					"(id TEXT," +
					"host TEXT," +
					"json TEXT)");
			// Create links table.
			db.execSQL("CREATE TABLE ips_links " +
					"(id TEXT," +
					"host TEXT," +
					"json TEXT)");
			// Create media table.
			db.execSQL("CREATE TABLE ips_media " +
					"(id TEXT," +
					"host TEXT," +
					"json TEXT)");
			// Create scripts table.
			db.execSQL("CREATE TABLE ips_scripts " +
					"(id TEXT," +
					"host TEXT," +
					"json TEXT)");
			// Create variables table.
			db.execSQL("CREATE TABLE ips_variables " +
					"(id TEXT," +
					"host TEXT," +
					"json TEXT)");
			// Create variable profiles table.
			db.execSQL("CREATE TABLE ips_varprofiles " +
					"(id TEXT," +
					"host TEXT," +
					"json TEXT)");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) throws SQLiteException
		{
			// On a database upgrade, drop all tables.
			db.execSQL("DROP TABLE IF EXISTS ips_hosts");
			db.execSQL("DROP TABLE IF EXISTS ips_objects");
			db.execSQL("DROP TABLE IF EXISTS ips_events");
			db.execSQL("DROP TABLE IF EXISTS ips_instances");
			db.execSQL("DROP TABLE IF EXISTS ips_links");
			db.execSQL("DROP TABLE IF EXISTS ips_media");
			db.execSQL("DROP TABLE IF EXISTS ips_scripts");
			db.execSQL("DROP TABLE IF EXISTS ips_variables");
			db.execSQL("DROP TABLE IF EXISTS ips_varprofiles");
			onCreate(db);
		}
	}
}